﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;

using Warehouse.Common.Entities;

namespace Warehouse.DataAccess.SqlClient
{
    public class UserDA : DABase<User>, IUserDA
    {
        protected override User getEntity(IDataReader reader)
        {
            return new User()
            {
                UserID = (int)reader["UserID"],
                UserCode = reader["UserCode"].ToString(),
                UserName = reader["UserName"].ToString(),
                Passwd = reader["Passwd"].ToString(),
                IsDisabled = reader["IsDisabled"].ToString() == "0",
                Note = reader["Note"].ToString(),
            };
        }

        public User Create()
        {
            return new User()
            {
                UserID = 0,
                UserCode = "",
                UserName = "",
                Passwd = "",
                IsDisabled = false,
                Note = "",
            };
        }

        public int Add(User toAdd, out int newID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var sql = "INSERT INTO Users (UserCode, UserName, Passwd, IsDisabled, Note)"
                      + " VALUES (@UserCode, @UserName, @Passwd, @IsDisabled, @Note)"
                      + "\n" + "SELECT @UserID = SCOPE_IDENTITY()";
            var cmd = db.GetSqlStringCommand(sql) as SqlCommand;
            //SqlCommand cmd = db.GetStoredProcCommand("pUserCreate") as SqlCommand;
            cmd.Parameters.AddWithValue("@UserCode", toAdd.UserCode);
            cmd.Parameters.AddWithValue("@UserName", toAdd.UserName);
            cmd.Parameters.AddWithValue("@Passwd", toAdd.Passwd);
            cmd.Parameters.AddWithValue("@IsDisabled", toAdd.IsDisabled);
            cmd.Parameters.AddWithValue("@Note", toAdd.Note);

            SqlParameter outParameter = cmd.Parameters.Add("@UserID", SqlDbType.Int, 0, "UserID");
            outParameter.Direction = ParameterDirection.Output;

            int affectedRows = db.ExecuteNonQuery(cmd);
            newID = (int)cmd.Parameters["@UserID"].Value;
            return affectedRows;
        }

        public int Update(User toUpdate)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var sql = "UPDATE Users SET UserCode=@UserCode, UserName=@UserName, Passwd=@Passwd"
                      + "\n" + ", IsDisabled=@IsDisabled, Note=@Note"
                      + "\n" + "WHERE UserID=@UserID";
            var cmd = db.GetSqlStringCommand(sql) as SqlCommand;
            //SqlCommand cmd = db.GetStoredProcCommand("pUserUpdate") as SqlCommand;
            cmd.Parameters.AddWithValue("@UserID", toUpdate.UserID);
            cmd.Parameters.AddWithValue("@UserCode", toUpdate.UserCode);
            cmd.Parameters.AddWithValue("@UserName", toUpdate.UserName);
            cmd.Parameters.AddWithValue("@Passwd", toUpdate.Passwd);
            cmd.Parameters.AddWithValue("@IsDisabled", toUpdate.IsDisabled);

            return db.ExecuteNonQuery(cmd);
        }

        public int DeleteByID(int id)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var sql = "DELETE FROM Users WHERE UserID=@UserID";
            var cmd = db.GetSqlStringCommand(sql) as SqlCommand;
            //SqlCommand cmd = db.GetStoredProcCommand("pUserDeleteByID") as SqlCommand;
            cmd.Parameters.AddWithValue("@UserID", id);

            return db.ExecuteNonQuery(cmd);
        }

        public User GetByID(int id)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var sql = "SELECT * FROM Users WHERE UserID=@UserID";
            var cmd = db.GetSqlStringCommand(sql) as SqlCommand;
            //SqlCommand cmd = db.GetStoredProcCommand("pUserLoadById") as SqlCommand;
            cmd.Parameters.AddWithValue("@UserID", id);

            return getSingle(db, cmd);
        }

        public User GetByCode(string userCode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var sql = "SELECT * FROM Users WHERE UserCode=@UserCode";
            var cmd = db.GetSqlStringCommand(sql) as SqlCommand;
            //SqlCommand cmd = db.GetStoredProcCommand("pUserLoadByCode") as SqlCommand;
            cmd.Parameters.AddWithValue("@UserCode", userCode);

            return getSingle(db, cmd);
        }

        public IEnumerable<User> Get(string condition)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var sql = "SELECT * FROM Users";
            if (!string.IsNullOrEmpty(condition))
            {
                sql += " WHERE " + condition;
            }
            var cmd = db.GetSqlStringCommand(sql) as SqlCommand;
            //SqlCommand cmd = db.GetStoredProcCommand("pUserLoadAll") as SqlCommand;

            return getMany(db, cmd);
        }
    }
}
